package com.bidevalution.business.job;

import com.bidevalution.business.factory.StepJobJdbcFactory;
import com.bidevalution.business.service.JdbcTemplateService;
import com.bidevalution.dao.entity.BeEvalutionExpert;
import com.bidevalution.dao.entity.BeSectionExpertInfo;
import com.bidevalution.dao.entity.BeSectionInfo;
import com.bidevalution.dao.entity.BeSectionStep;
import com.bidevalution.dao.entity.BeSectionStepJob;
import com.qtp.core.util.DateUtil;
import com.qtp.core.util.IdsUtil;
import com.qtp.core.util.OConvertUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 规费税金评审
 * Created by glodon on 2019-10-12.
 */
public class TaxFeesComputerJob implements StepJobJdbcFactory {
    private JdbcTemplateService jdbcTemplateService;

    @Override
    public void setJdbcTemplateService(JdbcTemplateService jdbcTemplateService) {
        this.jdbcTemplateService = jdbcTemplateService;
    }

    @Override
    public Boolean execute(BeSectionStepJob beSectionStepJob, String submitCode, String expertId,String layouCode)
            throws Exception {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("sectionStepId", beSectionStepJob.getSectionStepId());
        paramMap.put("submitCode", submitCode);
        paramMap.put("sectionId", beSectionStepJob.getSectionId());
        RowMapper<BeSectionInfo> rm = BeanPropertyRowMapper.newInstance(BeSectionInfo.class);
        BeSectionInfo beSectionInfo = jdbcTemplateService.selectForObject(" select\n" +
                "    seq_id, section_stage, section_code, section_name, bid_type, budget_money, bid_open_time, \n" +
                "    tenderee, agent_org_code, agent_name, bid_open_address, submit_time, pt_section_id, \n" +
                "    section_area, eval_method, td_projectId, create_time, update_time" +
                "    from be_section_info\n" +
                "     where seq_id=:sectionId ", paramMap, rm);
        paramMap.put("tdProjectId", beSectionInfo.getTdProjectid());
        //获取规费税金不符合数计算结果
        List<Map<String, Object>> enterList = jdbcTemplateService.selectForList("SELECT a.enterprise_id enterpriseId,b.enterprise_name enterpriseName, IFNULL(c.bfhs,0) bfhs\n" +
                "                FROM be_evalution_expert a \n" +
                "                LEFT JOIN be_section_enterprise b ON a.enterprise_id=b.seq_id and a.section_id = b.section_id \n" +
                "\t\t\t\t\t\t\t\tLEFT JOIN(select a.enterprise_id, count(1) as bfhs from(select m.enterprise_id,m.biin_name,n.otco_Name otcoName,n.mano_ManageNodeID,n.ManageNodeName manageNodeName,n.otco_OtherCostID,\n" +
                "            round(n.Rate,2) rate,n.Unit unit,'不符合招标费率要求：'+n.UnderRate as errMemo\n" +
                "            from td_bidinfo m \n" +
                "            inner join (select ProjectID,mano_ManageNodeID,otco_OtherCostID,ManageNodeName,\n" +
                "                 otco_Name,Type,Unit,BidID,Total,JSTotal,UnderRate,HyperRate,Rate\n" +
                "           from (\n" +
                "              select a.otco_ProjectID as ProjectID,c.mano_ManageNodeID,a.otco_OtherCostID ,\n" +
                "                case when c.mano_Type=2 then d.mano_Name+' - '+c.mano_Name else c.mano_Name end as ManageNodeName,\n" +
                "                a.otco_Name,a.otco_CostType as Type,'项' as Unit,a.enterprise_id as BidID  ,\n" +
                "                ifnull(a.otco_Total,0) as Total,\n" +
                "                case a.otco_CostType  \n" +
                "                                     when '4' then ifnull(e.RGFTotal,0)+ifnull(e.JXFTotal,0)+\n" +
                "                                                    ifnull(f.RGFTotal,0)+ifnull(f.JXFTotal,0)\n" +
                "                                     when '5' then ifnull(k.SJTotal,0)\n" +
                "                else 0 end as JSTotal,\n" +
                "               case when (ifnull(b.otco_Formula,'0')='0') or (b.otco_Formula='') then 0 else \n" +
                "                 cast(ifnull(b.otco_Formula,'0') as decimal(19,6)) end as UnderRate,\n" +
                "                case when (ifnull(b.otco_Formula,'0')='0') or (b.otco_Formula='') then 0 else \n" +
                "                  cast(ifnull(b.otco_Formula,'0') as decimal(19,6)) end as HyperRate,\n" +
                "                case when (ifnull(a.otco_Formula,'0')='0') or (a.otco_Formula='') then 0 else \n" +
                "                  cast(ifnull(a.otco_Formula,'0') as decimal(19,6)) end as Rate\n" +
                "              from td_OtherCost_Bid a\n" +
                "             left join td_OtherCost b on a.otco_ProjectID=b.otco_ProjectID  \n" +
                "                and a.otco_ManageNodeID=b.otco_ManageNodeID and a.otco_CostType=b.otco_CostType \n" +
                "                and b.otco_ProjectID=:tdProjectId\n" +
                "             left join td_ManageNode as c on a.otco_ProjectID=c.mano_ProjectID\n" +
                "                and a.otco_ManageNodeID=c.mano_ManageNodeID\n" +
                "                and c.mano_ProjectID=:tdProjectId\n" +
                "\t\t\t\t\t\t\t\tleft join td_ManageNode as d on c.mano_ProjectID=d.mano_ProjectID\n" +
                "                and c.mano_ParentID=d.mano_ManageNodeID\n" +
                "                and d.mano_ProjectID=:tdProjectId\n" +
                "              left join vi_budgetnodecost as e on a.otco_ProjectID=e.ProjectID \n" +
                "                and a.enterprise_id=e.BidID and a.otco_ManageNodeID=e.ManageNodeID\n" +
                "                and e.ProjectID=:tdProjectId\n" +
                "              left join vi_measuresnodecost as f on a.otco_ProjectID=f.ProjectID\n" +
                "                and a.enterprise_id=f.BidID and a.otco_ManageNodeID=f.ManageNodeID\n" +
                "                and f.ProjectID=:tdProjectId\n" +
                "\t\t\t\t\t\t\tleft join (select enterprise_id,cosu_ManageNodeID,sum(cosu_Total) as SJTotal\n" +
                "                  from td_CostSum_Bid where cosu_ProjectID=:tdProjectId and cosu_CostType in ('1','2','3','4') \n" +
                "                  group by enterprise_id,cosu_ManageNodeID) k \n" +
                "                on a.enterprise_id=k.enterprise_id and a.otco_ManageNodeID=k.cosu_ManageNodeID\n" +
                "              where a.otco_ProjectID=:tdProjectId and c.mano_Type<>0\n" +
                "                and (a.otco_CostType='4' or a.otco_CostType='5') \n" +
                "              union \n" +
                "              select a.otco_ProjectID as ProjectID,c.mano_ManageNodeID,a.otco_OtherCostID,\n" +
                "                c.mano_Name as ManageNodeName,\n" +
                "                a.otco_Name,a.otco_CostType as Type,'项' as Unit,a.enterprise_id as BidID,\n" +
                "                ifnull(a.otco_Total,0) as Total,\n" +
                "                case a.otco_CostType  \n" +
                "                                     when '4' then ifnull(h.RGFTotal,0)+ifnull(h.JXFTotal,0)\n" +
                "                                     when '5' then ifnull(d.ZTCSTotal,0)+ifnull(e.ZTQTTotal,0)+\n" +
                "                                                     ifnull(f.otco_Total,0)\n" +
                "                else 0 end as JSTotal,\n" +
                "                case when (ifnull(b.otco_Formula,'0')='0') or (b.otco_Formula='') then 0 else \n" +
                "                  cast(ifnull(b.otco_Formula,'0') as decimal(19,6)) end as UnderRate,\n" +
                "                case when (ifnull(b.otco_Formula,'0')='0') or (b.otco_Formula='') then 0 else \n" +
                "                  cast(ifnull(b.otco_Formula,'0') as decimal(19,6)) end as HyperRate,\n" +
                "                case when (ifnull(a.otco_Formula,'0')='0') or (a.otco_Formula='') then 0 else \n" +
                "                  cast(ifnull(a.otco_Formula,'0') as decimal(19,6)) end as Rate\n" +
                "              from td_OtherCost_Bid a \n" +
                "              left join td_OtherCost b on a.otco_ProjectID=b.otco_ProjectID \n" +
                "                and a.otco_ManageNodeID=b.otco_ManageNodeID and a.otco_CostType=b.otco_CostType \n" +
                "                and b.otco_ProjectID=:tdProjectId\n" +
                "             left join td_ManageNode as c on a.otco_ProjectID=c.mano_ProjectID\n" +
                "                and a.otco_ManageNodeID=c.mano_ManageNodeID\n" +
                "                and c.mano_ProjectID=:tdProjectId\n" +
                "              left join (select meno_ProjectID,enterprise_id,meno_ManageNodeID,sum(meno_Total) as ZTCSTotal \n" +
                "                  from td_MeasuresNode_Bid where meno_ProjectID=:tdProjectId\n" +
                "                  and meno_MeasuresNodeID='00000000-0000-0000-0000-000000000000'\n" +
                "                group by meno_ProjectID,enterprise_id,meno_ManageNodeID) d \n" +
                "                on a.otco_ManageNodeID=d.meno_ManageNodeID and a.otco_ProjectID=d.meno_ProjectID\n" +
                "               and a.enterprise_id=d.enterprise_id\n" +
                "             left join (select otno_ProjectID,enterprise_id,otno_ManageNodeID,sum(otno_Total) as ZTQTTotal\n" +
                "                  from td_OtherNode_Bid where otno_ProjectID=:tdProjectId\n" +
                "                  and otno_OtherNodeID='00000000-0000-0000-0000-000000000000'\n" +
                "                  group by otno_ProjectID,enterprise_id,otno_ManageNodeID) e \n" +
                "                on a.otco_ManageNodeID=e.otno_ManageNodeID and a.otco_ProjectID=e.otno_ProjectID \n" +
                "               and a.enterprise_id=e.enterprise_id \n" +
                "              left join td_OtherCost_Bid f on a.otco_ProjectID=f.otco_ProjectID \n" +
                "                and a.otco_ManageNodeID=f.otco_ManageNodeID \n" +
                "                and a.enterprise_id=f.enterprise_id and f.otco_CostType='4' \n" +
                "                and f.otco_ProjectID=:tdProjectId\n" +
                "              left join vi_measuresnodecost as h on a.otco_ProjectID=h.ProjectID \n" +
                "                and a.enterprise_id=h.BidID and a.otco_ManageNodeID=h.ManageNodeID\n" +
                "                and h.ProjectID=:tdProjectId\n" +
                "             where a.otco_ProjectID=:tdProjectId and c.mano_Type=0\n" +
                "                and (a.otco_CostType='4' or a.otco_CostType='5') \n" +
                "            ) X                                \n" +
                "           where (Type='4' and (UnderRate<>0 and round(Total,2)<round(JSTotal*UnderRate/100,2)*0.3-1)) \n" +
                "              or (Type='5' and ((UnderRate<>0 and round(Total,2)<=round(JSTotal*UnderRate/100,2)-1) or \n" +
                "                                   (HyperRate<>0 and round(Total,2)>=round(JSTotal*HyperRate/100,2)+1)))\n" +
                ") n on n.ProjectID = m.biin_ProjectID and n.BidID = m.enterprise_id\n" +
                "            where m.biin_ProjectID=:tdProjectId) a GROUP BY enterprise_id) c on b.seq_id = c.enterprise_id\n" +
                "\t\t\t\t\t\twhere b.section_id =:sectionId AND\ta.section_step_code='67' AND a.is_del='0' GROUP BY a.enterprise_id", paramMap);
        //保存计算结果
        saveResult(beSectionStepJob.getSectionId(), submitCode, enterList);
        return true;
    }

    private void saveResult(String sectionId, String submitCode, List<Map<String, Object>> enterList) throws Exception {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("sectionId", sectionId);
        RowMapper<BeSectionExpertInfo> rm1 = BeanPropertyRowMapper.newInstance(BeSectionExpertInfo.class);
        List<BeSectionExpertInfo> beSectionExpertInfoList = jdbcTemplateService.selectForList(" select\n" +
                "    seq_id, section_id, name, login_name, card_no, work_unit, phone, expert_type, area_type,\n" +
                "    is_avoid, avoid_reason, is_manager, update_time, create_time\n" +
                "    from be_section_expert_info\n" +
                "    where section_id=:sectionId", paramMap, rm1);
        RowMapper<BeSectionStep> rm2 = BeanPropertyRowMapper.newInstance(BeSectionStep.class);
        BeSectionStep beSectionStep = jdbcTemplateService.selectForObject(" select\n" +
                "    seq_id, section_id, step_code, step_name, parent_code, order_num, step_url, related_step_code, \n" +
                "    related_layout_code, related_column_code, related_step_id, create_time, update_time,load_function" +
                "    from be_section_step\n" +
                "     where section_id=:sectionId and step_code = '59'", paramMap, rm2);
        List<BeEvalutionExpert> beEvalutionExpertList = new ArrayList<>();
        BeEvalutionExpert beEvalutionExpert = null;
        for (BeSectionExpertInfo beSectionExpertInfo : beSectionExpertInfoList) {
            for (Map<String, Object> map : enterList) {
                beEvalutionExpert = new BeEvalutionExpert();
                beEvalutionExpert.setSeqId(IdsUtil.uuid());
                beEvalutionExpert.setExpertId(beSectionExpertInfo.getSeqId());
                beEvalutionExpert.setSectionId(sectionId);
                beEvalutionExpert.setColumnCode("bfhs");
                beEvalutionExpert.setColumnValue(OConvertUtils.getString(map.get("bfhs")));
                beEvalutionExpert.setEnterpriseId(OConvertUtils.getString(map.get("enterpriseId")));
                beEvalutionExpert.setSectionStepId(beSectionStep.getSeqId());
                beEvalutionExpert.setSectionStepCode(beSectionStep.getStepCode());
                beEvalutionExpert.setLayoutCode("Top");
                beEvalutionExpert.setSubmitCode(submitCode);
                beEvalutionExpert.setCreateTime(DateUtil.now());
                beEvalutionExpert.setUpdateTime(DateUtil.now());
                beEvalutionExpertList.add(beEvalutionExpert);

                beEvalutionExpert = new BeEvalutionExpert();
                beEvalutionExpert.setSeqId(IdsUtil.uuid());
                beEvalutionExpert.setExpertId(beSectionExpertInfo.getSeqId());
                beEvalutionExpert.setColumnCode(beSectionStep.getRelatedColumnCode());
                if (OConvertUtils.getInt(map.get("bfhs")) == 0) {
                    beEvalutionExpert.setColumnValue("1");
                } else {
                    beEvalutionExpert.setColumnValue("0");
                }
                beEvalutionExpert.setEnterpriseId(OConvertUtils.getString(map.get("enterpriseId")));
                beEvalutionExpert.setSectionId(sectionId);
                beEvalutionExpert.setSectionStepId(beSectionStep.getRelatedStepId());
                beEvalutionExpert.setSectionStepCode(beSectionStep.getRelatedStepCode());
                beEvalutionExpert.setLayoutCode(beSectionStep.getRelatedLayoutCode());
                beEvalutionExpert.setSubmitCode(submitCode);
                beEvalutionExpert.setCreateTime(DateUtil.now());
                beEvalutionExpert.setUpdateTime(DateUtil.now());
                beEvalutionExpertList.add(beEvalutionExpert);
            }
        }
        //批量转数组
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(beEvalutionExpertList);
        jdbcTemplateService.bathInsert(" insert into be_evalution_expert (seq_id, section_step_id, section_id,\n" +
                "    column_code, expert_id, column_value,submit_code,row_key,layout_code,enterprise_id,section_step_code,create_time,\n" +
                "    update_time)\n" +
                "    values(:seqId, :sectionStepId, :sectionId,:columnCode, :expertId, :columnValue,:submitCode,:rowKey," +
                "   :layoutCode,:enterpriseId,:sectionStepCode,:createTime,:updateTime)", beanSources);
    }
}
